package gwtappcontainer.server.apps.insight;

import gwtappcontainer.server.Utils;
import gwtappcontainer.server.apis.APIException;
import gwtappcontainer.shared.apis.APIResponse.Status;
import gwtappcontainer.shared.apis.insight.Center;
import gwtappcontainer.shared.apis.insight.Member;
import gwtappcontainer.shared.apis.insight.UnlistedProgram;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Calendar;

public class MemberRepository {
	
	public static Member get(String email) {		
		return getByIdOrEmail(0, email);	
	}
	
	public static Member get(long memberId) {		
		return getByIdOrEmail(memberId, null);	
	}
	
	public static Member getByIdOrEmail(long memberId, String email) {
		
		if ((memberId == 0) && (email == null))
			throw new APIException(Status.ERROR_RESOURCE_INCORRECTLY_SPECIFIED, 
					"Either id or email has to be specified");				
        
	    try {
	    	String sql;
	    	if (memberId == 0)
	    		sql = "select member_id, first_name, primary_center_id, center, middle_name, last_name, email, " +
            		"phone, home_address, office_address, home_phone, office_phone, singapore_nric " + 
            		"from members, centers where email = ? and primary_center_id = center_id";
	    	else {
	    		sql = "select member_id, first_name, primary_center_id, center, middle_name, last_name, email, " +
	            		"phone, home_address, office_address, home_phone, office_phone, singapore_nric " + 
	            		"from members, centers where member_id = ? and primary_center_id = center_id";
			}
           
            Member member = null;
           
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
                try (PreparedStatement ps = connection.prepareStatement(sql)) {   
                	if (memberId == 0)
                		ps.setString(1, email.toLowerCase());                		
                	else {
                		ps.setLong(1, memberId);						
					}
                   
                    try (ResultSet resultSet = ps.executeQuery()) {                                        
                        while (resultSet.next()) {
                            member = new Member();
                            member.id = resultSet.getInt("member_id");
                            member.firstName = resultSet.getString("first_name");
                            member.primaryCenter.id = resultSet.getInt("primary_center_id");
                            member.primaryCenter.name = resultSet.getString("center");
                            member.lastName = resultSet.getString("last_name");
                            member.middleName = resultSet.getString("middle_name");    
                            member.email = resultSet.getString("email");
                            member.phone = resultSet.getString("phone");
                            member.homeAddress = resultSet.getString("home_address");
                            member.officeAddress = resultSet.getString("office_address");
                            member.homePhone = resultSet.getString("home_phone");
                            member.officePhone = resultSet.getString("office_phone");
                            member.singaporeNRIC = resultSet.getString("singapore_nric");
                        }
                    }                                       
                }
                
                if (member == null)
                	return null;
                
                sql = "select mc.center_id, center from member_centers mc, centers c where " +  
            			"member_id = ? and mc.center_id = c.center_id order by center";
                try (PreparedStatement ps = connection.prepareStatement(sql)) {   
                	ps.setLong(1, member.id);
                   
                    try (ResultSet resultSet = ps.executeQuery()) {                                        
                        while (resultSet.next()) {
                           Center center = new Center();
                           center.id = resultSet.getInt(1);
                           center.name = resultSet.getString(2);
                           member.additionalCenters.add(center);
                        }
                    }                                        
                }
                sql = "select unlisted_program_id, up.program_type_id, month, year, teacher, venue " +
                		"from unlisted_programs up, program_types pt where " +  
            			"member_id = ? and up.program_type_id = pt.program_type_id order by (year + month)";
                try (PreparedStatement ps = connection.prepareStatement(sql)) {   
                	ps.setLong(1, member.id);
                   
                    try (ResultSet resultSet = ps.executeQuery()) {                                        
                        while (resultSet.next()) {
                        	UnlistedProgram unlistedProgram = new UnlistedProgram();
                        	unlistedProgram.id = resultSet.getInt(1);
                        	unlistedProgram.programTypeId = resultSet.getInt(2);
                        	unlistedProgram.month = resultSet.getInt(3);
                        	unlistedProgram.year = resultSet.getInt(4);
                        	unlistedProgram.teacher = resultSet.getString(5);
                        	unlistedProgram.venue = resultSet.getString(6);
                        	
                        	member.unlistedPrograms.add(unlistedProgram);
                        }
                    }                                        
                }                
                return member;
            }           	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }              
	}
	
	public static void add(Member member) {
		
		if (member.email == null)
			throw new APIException(Status.ERROR_RESOURCE_INCORRECTLY_SPECIFIED, 
					"Email cannot be empty");
		
		if ((member.firstName == null) || (member.firstName.equals("")))
			throw new APIException(Status.ERROR_RESOURCE_INCORRECTLY_SPECIFIED, 
					"First name cannot be empty");
					
		if (null != get(member.email))
			 throw new APIException(Status.ERROR_RESOURCE_ALREADY_EXISTS, 
					 "Member [" + member.email + "] already exists");
		 
		try {
            String sql = "insert into members values (0, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";          
           
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
                try (PreparedStatement ps = connection.prepareStatement(sql)) {                       
                    
                    ps.setString(1, member.firstName);
                    ps.setLong(2, member.primaryCenter.id);
                    ps.setString(3, member.middleName);
                    ps.setString(4, member.lastName);
                    ps.setString(5, member.email.toLowerCase());
                    ps.setString(6, member.phone);
                    ps.setString(7, member.homeAddress);
                    ps.setString(8, member.officeAddress);
                    ps.setString(9, member.homePhone);
                    ps.setString(10, member.officePhone);
                    ps.setString(11, member.singaporeNRIC);
                    
                    ps.executeUpdate();                                                                            
                }                               
            }           	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }             
	 }	

	public static void addCenter(long memberId, long centerId) {
		try {
            String sql = "insert into member_centers values (?, ?)";          
           
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
                try (PreparedStatement ps = connection.prepareStatement(sql)) {                       
                    
                    ps.setLong(1, memberId);
                    ps.setLong(2, centerId);                    
                    ps.executeUpdate();                                                                            
                }               
            }           	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }          
	}
	
	public static void deleteCenter(long memberId, long centerId) {
		try {
            String sql = "delete from member_centers where member_id = ? and center_id = ?";          
           
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
                try (PreparedStatement ps = connection.prepareStatement(sql)) {                       
                    
                    ps.setLong(1, memberId);
                    ps.setLong(2, centerId);                    
                    ps.executeUpdate();                                                                            
                }               
            }           	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }          
	}

	public static void addUnlistedProgram(long memberId, UnlistedProgram unlistedProgram) {
		
		if (unlistedProgram.month < 1 || unlistedProgram.month > 12) 
			throw new APIException(Status.ERROR_RESOURCE_INCORRECTLY_SPECIFIED, 
					"Month should be between 1 and 12");				
		
		if (unlistedProgram.year < 1970 || unlistedProgram.year > Calendar.getInstance().get(Calendar.YEAR)) 
			throw new APIException(Status.ERROR_RESOURCE_INCORRECTLY_SPECIFIED, 
					"Year should be after 1970 and lesser than or equal to current year");
						
		try {
            String sql = "insert into unlisted_programs values (0, ?, ?, ?, ?, ?, ?, ?)";          
           
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
                try (PreparedStatement ps = connection.prepareStatement(sql)) {                       
                    
                    ps.setInt(1, unlistedProgram.programTypeId);
                    ps.setInt(2, unlistedProgram.month);                    
                    ps.setInt(3, unlistedProgram.year);
                    ps.setString(4, unlistedProgram.teacher);
                    ps.setString(5, unlistedProgram.venue);
                    ps.setLong(6, memberId);
                    ps.executeUpdate();                                                                            
                }               
            }           	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }  		
	}
	
	public static void deleteUnlistedProgram(int unlistedProgramId) {	
		try {
            String sql = "delete from unlisted_programs where unlisted_program_id = ?";          
           
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
                try (PreparedStatement ps = connection.prepareStatement(sql)) {                                           
                    ps.setInt(1, unlistedProgramId);                   
                    ps.executeUpdate();                                                                            
                }                
            }           	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }  		
	}
}



